def summation():
    file = 'test1.xlsx'
    # 创建工作空间
    workbook = xlrd.open_workbook(file)
    # 选取第0个表
    sheet1 = workbook.sheet_by_index(0)
    # 创建工作空间
    workbook2 = openpyxl.load_workbook(file)
    # 选取Sheet1表
    sheet2 = workbook2['Sheet1']
    # 在types中添加一列，以获取全部有效数值
    types.append(22)
    types2.append('V')
    # 设置列和动态变量
    cols_sum = locals()
    # 设置列值动态变量
    cols_list = locals()
    data_2 = []
    a = 0
    for i in types:
        # 创建动态变量列表
        cols_list['cols_list_' + str(i - 1)] = []
        # 获取i-1列的值
        cols_list['cols_list_' + str(i - 1)] = (sheet1.col_values(i - 1))
        # 删除第0个值，为了删除前三个标题
        cols_list['cols_list_' + str(i - 1)].pop(0)
        cols_list['cols_list_' + str(i - 1)].pop(0)
        cols_list['cols_list_' + str(i - 1)].pop(0)
        # 有效数值的行数
        lc = len(cols_list['cols_list_' + str(i - 1)])
        # 循环求和
        for j in cols_list['cols_list_' + str(i - 1)]:
            # 用于检测单元格是否为空
            data_1 = sheet2.cell(lc + 3, i).value
            data_2 = re.findall(r'.+', str(data_1))
            print(data_1, data_2, j)
            # 空值跳过
            if j == '' or j == ' ':
                print(1)
                pass
            # 单元格为空值加入数值
            elif data_1 is None or data_1 == ' ':
                print(2)
                sheet2.cell(lc + 3, i).value = j

                print(data_1)
                print('j' + str(type(j)))
            # 求和
            else:
                print(3)
                print(data_1, data_2)
                data = float(''.join(data_2))
                j_1 = re.findall(r'.+', str(j))
                j_2 = float(''.join(j_1))
                print('j_1:' + j_2, data)
                sum2 = data + j_2

                print(j_1, j, data, sum2, type(j), type(data), type(sum))
                print(j_2, data, sum2, type(j_2), type(data), type(sum))
                sheet2.cell(lc + 3, i).value = sum2
                # float无法计算len，先变成str再取整数部分
                sumlen = len(str(sum2).split('.')[0])
                # 自适应列宽
                sheet2.column_dimensions[types2[a]].width = sumlen + 3.5

                workbook2.save('test2.xlsx')
                workbook2.save(fileOutPath[:-5] + '土地分类表' + '.xlsx')

        a = a + 1

    workbook2.save('test2.xlsx')
    workbook2.save(fileOutPath[:-5] + '土地分类表' + '.xlsx')
    print(fileOutPath[:-5] + '土地分类表' + '.xlsx' + '完成')
